package database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import server.gui.*;

public class JDBC{
	

	public static Connection connection;
	

	public JDBC() {
		try 
		{
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        } catch (Exception ex) {/* handle the error*/}
        
        try 
        {
        	connection = DriverManager.getConnection("jdbc:mysql://localhost/aes",Server_GUI.sqlUser,Server_GUI.sqlPW);
     	} 
        catch (SQLException ex){/* handle any errors*/
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        }
        System.out.println("SQL connection succeed");
	}// end constructor
	
	public static boolean selectStatment(String sql, Object[] result, int numOfColumns){
		Statement stmt;
		try 
		{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			if( rs.next())
				for(int i=0; i<numOfColumns; i++)
		 			result[i]=rs.getObject(i+1);
			rs.close();
			return true;
		}// end try
		catch (SQLException e){
			e.printStackTrace();
		}
		return false;
	}// end selectStatment
	
	public static boolean selectStatmentMatrix(String sql, Object[][] result, int numOfColumns, int numOfRows){
		Statement stmt;
		try 
		{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			int j=0;
			while( rs.next())
			{
				for(int i=0; i<numOfColumns; i++)
		 			result[j][i]=rs.getObject(i+1);
				j++;
			}	
			rs.close();
			return true;
		}// end try
		catch (SQLException e){
			e.printStackTrace();
		}
		return false;
	}// end selectStatment
	
	public static boolean insertStatment(String sql){
		try 
		{
	 		PreparedStatement ps =  connection.prepareStatement(sql);
	 		if(ps.executeUpdate()==1)
	 			return true;
		}
 		catch (SQLException e){
			e.printStackTrace();
		}
		return false;
	}// insertStatment
	
	public static boolean existStatment(String key,String column, String table){
		
		Statement stmt;
		ResultSet rs;
		
		try {
				stmt = connection.createStatement();
				rs = stmt.executeQuery("SELECT count(*) FROM " + table + " WHERE "+ column + "=" + key +";" );
				rs.next();
				if (rs.getInt(1) == 1)
					return true;
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return false;
		
	}
	
	public static boolean updateStatment(String table, String column, String key, String row, String cond){
		
		Statement stmt;
		
		try {
				stmt = connection.createStatement();
				stmt.executeUpdate("UPDATE " + table + " SET " + column + "=" + key + " WHERE " + row + "=" + cond +";");
		
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return false;
		
	}
	public static boolean updateStatment(String sql){
		
		Statement stmt;
		
		try {
				stmt = connection.createStatement();
				stmt.executeUpdate(sql);
		
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return false;
		
	}
	
	public static boolean updateStatmentCloumn(String table, String column, String key){
		
		Statement stmt;
		
		try {
				stmt = connection.createStatement();
				stmt.executeUpdate("UPDATE " + table + " SET " + column + "=" + key +";");
		
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return false;
		
	}
	
	public static boolean updateStatmentCloumn(String table, String column, String value, String condition){
		
		Statement stmt;
		
		try {
				stmt = connection.createStatement();
				stmt.executeUpdate("UPDATE " + table + " SET " + column + "=" + value +" WHERE "+condition+";");
				return true;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return false;
		
	}
	
	public static int countStatment(String sql){
		int count;
		Statement stmt;
		ResultSet rs;
		try {
			stmt = connection.createStatement();
			
				rs = stmt.executeQuery(sql);
			
				
			rs.next();
			count = rs.getInt(1);
			return count;
		
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return -1;
	}
	
	public static void deleteStatment(String sql){
		
		Statement stmt;
		
		try {
			stmt = connection.createStatement();
			
			stmt.executeUpdate(sql);
			
				
			
		
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		
	}

}// end class JDBC